package org.pybaldaj.bot;

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBPopulator {

	static Connection conn = null;

	private static void insertWord(String word) {
		try {
			Statement st = conn.createStatement();
			st.executeUpdate("INSERT INTO WORDS(WORD) " + "VALUES ('" + word
					+ "')");
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
	}
	
	private static void insertCombo(String combo, int wordId) {
		try {
			Statement st = conn.createStatement();
			st.executeUpdate("INSERT INTO COMBO(COMBO,WORD_ID) " + "VALUES ('" + combo
					+ "', "+ wordId +")");
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
	}

	private static int getWordId(String word) {
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("SELECT ID FROM WORDS WHERE WORD='"
					+ word + "'");
			rs.next();
			int wordId = rs.getInt(1);
			if (wordId == 0) {
				insertWord(word);
				wordId = getWordId(word);
			}
			return wordId;
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		}
		return 0;
	}

	private static void processDictionary() {
		try {
			FileInputStream fstream = new FileInputStream(
					"src/main/resources/dictionary.txt");
			DataInputStream in = new DataInputStream(fstream);
			BufferedReader br = new BufferedReader(new InputStreamReader(in));
			String strLine;
			while ((strLine = br.readLine()) != null) {
				String word = strLine.trim();
				int wordId = getWordId(word);
				System.out.println("Word " + word + ": " + wordId);
				for (short i = 0; i <= word.length()-4; ++i) {
					String combo = word.substring(i, i+4);
					System.out.println(combo);
					insertCombo(combo, wordId);
				}

			
				
				
			}
			in.close();
		} catch (Exception e) {// Catch exception if any
			System.err.println("Error: " + e.getMessage());
		}
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {

		try {
			Class.forName("org.h2.Driver").newInstance();
			String url = "jdbc:h2:~/devel/pybaldaj/dictionary";
			conn = DriverManager.getConnection(url, "sa", "");

			processDictionary();

		} catch (ClassNotFoundException ex) {
			System.err.println(ex.getMessage());
		} catch (IllegalAccessException ex) {
			System.err.println(ex.getMessage());
		} catch (InstantiationException ex) {
			System.err.println(ex.getMessage());
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} finally {
			if (null != conn) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}

	}

}
